package tsli.acm.dao;

import java.util.List;
import java.util.Map;
import tsli.acm.database.Database;
import tsli.acm.model.TempCount;

public class TempCountAgentDAO {

	private Database db;

	public TempCountAgentDAO(Database db) {
		this.db = db;
	}

	/** 
	 * this count agent and insert agent data. to tsli_temp_count
	 * @param closYm
	 * @return
	 */
	public boolean insertAgentByCondition(String closYm){
		boolean act = false;
		try {
			
			this.deleteAgent(closYm);
			
			GACommissionPositiveDAO positiveDAO  = new GACommissionPositiveDAO(db);
			List<Map<String, Object>> listsAgent = positiveDAO.countAgent(closYm);
			for (int i = 0; i < listsAgent.size(); i++) {
				Map<String, Object> item = listsAgent.get(i);
				TempCount  tempCount  = new TempCount();
				tempCount.setVaYyyyMm(item.get("YYYYMM").toString());
				tempCount.setVaTypeMode(item.get("PAYMENT_MODE").toString());
				tempCount.setVaPeNoSoli(item.get("PE_NO_SM").toString());
				tempCount.setVaCountPersion(Double.parseDouble(item.get("COUNT_PERSION").toString()));
				
				System.out.println("Infor : count agent and insert agent complete.");
				
				this.insertAgent(tempCount);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return act;
		
	}
	
	
	/**
	 * this insert data
	 * @param count
	 * @return
	 */
	public boolean insertAgent(TempCount count){ 
		boolean act = false;
		try {
			String sql = "insert into TSLI_TEMP_COUNT ( YYYYMM , TYPE_MODE , PE_NO_SOLI , COUNT_PERSION )"
					+ "VALUES ( ? , ? , ? , ? ) ";
			this.db.insert(sql, count.getVaYyyyMm() , count.getVaTypeMode() , count.getVaPeNoSoli() , count.getVaCountPersion());
			act = true;
		} catch (Exception ex) {
			ex.printStackTrace();
			act = false;
		}
		return act;
	}
	
	/**
	 * 
	 * @return
	 */
	public boolean deleteAgent(String closYm){ 
		boolean act = false;
		try { 
			String sql = " DELETE FROM TSLI_TEMP_COUNT WHERE YYYYMM = ? ";
			this.db.insert(sql, closYm);
			System.out.println("Info : delete agent befor complete.");
			act = true;
		}catch(Exception ex){ 
			ex.printStackTrace();
		}
		return act;
	}
	
	/**
	 * remove data detail
	 * @param closYm
	 * @return
	 */
	public boolean deleteAgentDetail(String closYm){ 
		boolean act = false;
		try { 
			String sql = " DELETE FROM TSLI_TEMP_AGENT_DETAIL WHERE YYYYMM = ? ";
			this.db.insert(sql, closYm);
			System.out.println("Info : delete agent detail befor complete.");
			act = true;
		}catch(Exception ex){ 
			ex.printStackTrace();
		}
		return act;
	}

	/** 
	 * 
	 * @param closYm
	 * @return
	 */
	public boolean insertDetailAgent(String closYm){ 
		boolean act = false;
		try { 
			
			this.deleteAgentDetail(closYm);
			
			String vaSql = " INSERT INTO TSLI_TEMP_AGENT_DETAIL "+
					 	   " SELECT  a.pe_no_sm,a.pe_no_col,a.po_no, " +
						   " 1*(SUM(a.FYP)) AS SUM_FYP , " +
						   " SUBSTR(a.PAY_MOD_CD,7,2) as payment_mode  , '" + closYm +"' AS YYYYMM " +
						   " FROM tsli_ga_commission_positive  a " + 
						   " LEFT JOIN  ( " +
						   " SELECT pe_no FROM tsli_ga_agent_structure " + 
						   " WHERE  LCNS_RNWL_YMD IS NOT NULL " +
						   " ) d ON a.pe_no_col = d.pe_no " +
						   " WHERE SUBSTR(a.PAY_MOD_CD, 7 , 2) ='12' " +
						   " AND a.CONT_MMS = 1 " +
						   " AND a.CLOS_YM ='"+ closYm + "' " +
						   " AND a.COVER_PRD <> 1 " + 
						   " AND a.PAY_PRD <> 1 " +
						   " GROUP BY a.pe_no_sm , a.pe_no_col , a.po_no , SUBSTR(a.PAY_MOD_CD,7,2) " + 
						   " HAVING 1*(SUM(a.FYP)) >=7000 " +
   						   " UNION ALL " +
   							" SELECT a.pe_no_sm,a.pe_no_col,a.po_no,  " +
   							" 2*(SUM(a.FYP)) AS SUM_FYP, " +
   							" SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode  , '" + closYm +"' AS YYYYMM " +
   							" FROM tsli_ga_commission_positive  a " +
   							" LEFT JOIN ( " + 
   							" SELECT pe_no FROM tsli_ga_agent_structure " + 
   							" WHERE  LCNS_RNWL_YMD IS NOT NULL " + 
   							" )  d " +
   							" ON a.pe_no_col = d.pe_no " +
   							" WHERE SUBSTR(a.PAY_MOD_CD, 7 , 2) ='06' " +
   							" and a.CONT_MMS =1 " +
   							" and a.CLOS_YM ='" + closYm + "' " +
   							" GROUP BY a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2) " +
   							" Having 2*(SUM(a.FYP)) >=12000 " + 
  
  							" UNION ALL " + 
  
  							" select a.pe_no_sm,a.pe_no_col,a.po_no,  " +
  							" 4*(SUM(a.FYP)) AS SUM_FYP, " + 
  							" SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode , '" + closYm +"' AS YYYYMM " +
  							" from tsli_ga_commission_positive  a " +
  							" left join ( " +
  							" SELECT pe_no FROM tsli_ga_agent_structure " + 
  							" WHERE  LCNS_RNWL_YMD IS NOT NULL " + 
  							" )  d " +
  							" on a.pe_no_col = d.pe_no  " +
  							" WHERE SUBSTR(a.PAY_MOD_CD, 7 ,2) ='03' " +
  							" AND a.CONT_MMS = 1 " +
  							" AND a.CLOS_YM ='"+closYm + "' " +
  							" GROUP BY a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2) " + 
  							" Having 4*(SUM(a.FYP))  >=12000 " +
  
  							" UNION ALL " +
  
  							" select a.pe_no_sm,a.pe_no_col,a.po_no, " +
  							" 12*(SUM(a.FYP)) AS SUM_FYP, " +
  							" SUBSTR(a.PAY_MOD_CD, 7 , 2) as payment_mode , '" + closYm +"' AS YYYYMM " +
  							" from tsli_ga_commission_positive  a " +
  							" left join ( " +
  							" SELECT pe_no FROM tsli_ga_agent_structure " + 
  							" WHERE  LCNS_RNWL_YMD is not null " +
  							" ) d " +
  							" on a.pe_no_col = d.pe_no " +
  							" where " + 
  							" SUBSTR(a.PAY_MOD_CD, 7 , 2) = '01' " + 
  							" and a.CONT_MMS =1 " +
  							" and a.CLOS_YM ='201403' " +
	
							" GROUP BY a.pe_no_sm,a.pe_no_col,a.po_no , SUBSTR(a.PAY_MOD_CD, 7 , 2) " +
							" Having 12*(SUM(a.FYP)) >=12000 ";
		
			System.out.println("Info : summary detail agent complete.");
			this.db.insert(vaSql);
			act = true;
		} catch (Exception ex){ 
			ex.printStackTrace();
		}
		return act;
	}
	
	
	/**
	 * 
	 * @param vaPeNoSm
	 * @param close
	 * @return
	 */
	public int countAgentByAG(String vaPeNoSm , String close){
		return 0;
	}
}
